---
sidebar_label: Query performance
sidebar_position: 10
description: Performance of Hasura GraphQL queries on MS SQL Server
keywords:
  - hasura
  - docs
  - ms sql server
  - schema
  - queries
  - performance
---

import Thumbnail from '@site/src/components/Thumbnail';
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

# MS SQL Server: Query Performance

## Introduction

Sometimes queries can become slow due to large data volumes or levels of nesting. This page explains how to identify the
query performance, and how queries can be optimized.

:::info Note

Only queries and subscriptions can be analyzed. Mutations are not supported.

Stored procedures cannot be analyzed due to limitations in MS SQL Server.

:::

## Analyzing query performance {#ms-sql-server-analyzing-query-performance}

Let's say we want to analyze the following query:

```graphql
query {
  authors(where: { name: { _eq: "Mario" } }) {
    rating
  }
}
```

In order to analyze the performance of a query, you can click on the `Analyze` button on the Hasura Console:

<Thumbnail src="/img/queries/analyze-query.png" alt="Query analyze button on Hasura Console" width="800px" />

The following query execution plan is generated:

<Thumbnail
  src="/img/queries/query-analysis-before-index.png"
  alt="Execution plan for Hasura GraphQL query"
  width="800px"
/>

We can see that a sequential scan is conducted on the `authors` table. This means that MS SQL Server goes through every
row of the `authors` table in order to check if the author's name equals "Mario". The `cost` of a query is an arbitrary
number generated by MS SQL Server and is to be interpreted as a measure of comparison rather than an absolute measure of
something.

Read more about query performance analysis in the
[MS SQL Server explain statement docs](https://docs.microsoft.com/en-us/sql/t-sql/queries/explain-transact-sql?view=azure-sqldw-latest).

## Query optimization

### Using MS SQL indexes {#ms-sql-server-data-validation-mssql-indexes}

[MS SQL Server indexes](https://docs.microsoft.com/en-us/sql/relational-databases/indexes/indexes?view=sql-server-ver15)
are special lookup tables that MS SQL Server can use to speed up data lookup. An index acts as a pointer to data in a
table, and it works very similar to an index in the back of a book. If you look in the index first, you'll find the data
much quicker than searching the whole book (or - in this case - database).

Let's say we know that `authors` table is frequently queried by `name`:

```graphql
query {
  authors(where: { name: { _eq: "Mario" } }) {
    rating
  }
}
```

We've seen in the [above example](#ms-sql-server-analyzing-query-performance) that by default MS SQL Server conducts a
sequential scan i.e. going through all the rows. Whenever there is a sequential scan, it can be optimized by adding an
index.

The following statement sets an index on `name` in the `authors` table.

```plsql
CREATE INDEX ON authors (name);
```

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

An index can be added in the `Data -> SQL` tab in the Hasura Console.

</TabItem>
<TabItem value="cli" label="CLI">

[Create a migration manually](/migrations-metadata-seeds/manage-migrations.mdx#create-manual-migrations) and add your
create index statement to the `up.sql` file. Also, add an SQL statement to revert that statement to the `down.sql` file
in case you need to [roll back](/migrations-metadata-seeds/manage-migrations.mdx#roll-back-migrations) the migration.

Apply the migration by running:

```bash
hasura migrate apply
```

</TabItem>
<TabItem value="api" label="API">

You can add an index by making an API call to the
[schema_run_sql Metadata API](/api-reference/schema-api/run-sql.mdx#schema-run-sql):

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
   "type": "run_sql",
   "args": {
      "source": "<db-name>",
      "sql": "<create index statement>"
   }
}
```

</TabItem>
</Tabs>

Let's compare the performance analysis to [the one before adding the index](#ms-sql-server-analyzing-query-performance).
What was a `sequential scan` in the example earlier is now an `index scan`. `Index scans` are usually more performant
than `sequential scans`. We can also see that the `cost` of the query is now lower than the one before we added the
index.

<Thumbnail
  src="/img/queries/query-analysis-after-index.png"
  alt="Execution plan for Hasura GraphQL query"
  width="800px"
/>

:::info Note

In some cases sequential scans can still be faster than index scans, e.g. if the result returns a high percentage of the
rows in the table. MS SQL Server comes up with multiple query plans and takes the call on what kind of scan would be
faster.

:::
